IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DDMSAnswerConversion]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[DDMSAnswerConversion]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

      
CREATE procedure dbo.DDMSAnswerConversion      
as      
begin      
      
	DECLARE @TempValues TABLE (
		[RowNum] INT IDENTITY (1,1),
		[Id] INT,
		[OptionValue] Varchar(30)
	)
	
	insert into @TempValues (Id, OptionValue)
	select a.id, op.optionvalue 
	from Answer A
		Inner join QuestionnaireAnswer QA on QA.Id = A.QuestionnaireAnswerId
		inner join QuestionnaireVersion QV on QA.QuestionnaireVersionid = QV.id
		inner join QuestionnaireItem QI on (QI.Versionid = QV.Id and A.Name = QI.itemname)
		inner join itemoption Op on (QI.id = Op.ItemId and a.value=op.optiontext)
	where QI.itemtype = 'DropDownMultiSelect'

	DECLARE @answerCount INT
	DECLARE @recordIndex INT 
	
	SELECT @answerCount = count(*) FROM @TempValues 
	SET @recordIndex = 1
	
	WHILE (@recordIndex <= @answerCount)
	BEGIN
		DECLARE @answerId INT
		DECLARE @optionValue varchar(30)
		Select @answerid = id, @optionvalue = optionvalue from @TempValues where rownum = @recordIndex
		Update Answer Set
			value = @optionValue
		where id = @answerid

		SET @recordIndex = @recordIndex + 1
	END
end      

GO
